package edzis.translatebuddy.model {
	
	import edzis.translatebuddy.model.data.SessionDataProvider;
	import edzis.translatebuddy.model.data.WordDataProvider;
	import edzis.translatebuddy.model.vo.SearchProfileVO;
	import edzis.translatebuddy.model.vo.SessionVO;
	import edzis.translatebuddy.model.vo.WordVO;
	import fl.data.DataProvider;
	import flash.data.SQLConnection;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.errors.SQLError;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.net.Responder;
	import org.puremvc.as3.multicore.interfaces.IProxy;
	import org.puremvc.as3.multicore.patterns.proxy.Proxy;

		
	
	[Bindable]
	public class DataProxy extends Proxy implements IProxy {
	
		public static const NAME	:String = "DataProxy";
		
		public static const READY	:String = "onDataProxyReady";
		
		private var _dbFile				:File;
		private var _dbConn				:SQLConnection;
		public var allSessions			:SessionDataProvider;
		public var allSearchProfiles	:DataProvider;
		public var wordsSession			:WordDataProvider;
		private var _selectedSession	:int;
		private var _resultUsage			:String;
		
		private static const USAGE_ADD		:String = "USAGE_ADD";
		private static const USAGE_UPDATE	:String = "USAGE_UPDATE";
		
		private var getAllSessionsStatement				:SQLStatement;
		private var getAllSearchProfilesStatement		:SQLStatement;
		private var addSearchProfileStatement			:SQLStatement;
		private var getSearchProfileStatement			:SQLStatement;
		private var createSessionStatement				:SQLStatement;
		private var getAllWordsInSesionStatement		:SQLStatement;
		private var getSingleWordStatement				:SQLStatement;
		private var addWordStatement					:SQLStatement;
		private var addTranslationStatement				:SQLStatement;
		private var addWordToSesionStatement			:SQLStatement;
		
		private var appProxy:AppProxy;
		private var _dbExists							:Boolean;


		public function DataProxy() {
			super(NAME, null );
			
			allSessions = new SessionDataProvider();
			allSearchProfiles = new DataProvider();
			wordsSession = new WordDataProvider();
		}
		
		override public function onRegister():void {
			super.onRegister();
			appProxy = facade.retrieveProxy(AppProxy.NAME) as AppProxy;
		}
		
		
	/************************************
	/*	PUBLIC API
	/***********************************/
		public function connectToDb():void {
			ArthropodDebug.log("connectToDb ");
			_dbFile = File.applicationStorageDirectory.resolvePath("translatebuddy.db");
			_dbExists = _dbFile.exists;
			ArthropodDebug.log("_dbExists " + _dbExists);
			_dbConn = new SQLConnection();
			_dbConn.addEventListener(SQLEvent.OPEN, onDbOpened);
			_dbConn.addEventListener(SQLErrorEvent.ERROR, onDbOpenError);
			_dbConn.open(_dbFile);
			ArthropodDebug.log("_dbExists " + _dbExists);
		}
		
	/************************************
	/*	SQL STATEMENT CALLS
	/***********************************/
	
	//	SESSIONS
		public function getAllSessions():void {
			ArthropodDebug.log("getAllSessions");
			if(getAllSessionsStatement)
			getAllSessionsStatement.execute(-1,new Responder(onAllSessionsResult, onError));
		}
		
		public function tryCreateSession(title:String):void {
			var newSession:SessionVO;
			newSession = allSessions.getSessionByTitle(title);
			if (newSession == null) {
				createSessionStatement.parameters["@title"] = title;
				createSessionStatement.execute( -1, new Responder(onSessionCreated, onError));
				appProxy.appState = AppProxy.ADD_WORD;
			}
		}
		
		
	//	SEARCH PROFILES
		public function getAllSearchProfiles():void {
			ArthropodDebug.log("getAllSearchProfiles");
			if(getAllSearchProfilesStatement)
			getAllSearchProfilesStatement.execute(-1,new Responder(onAllSearchProfilesResult, onError));
		}
		
		public function addSearchProfile(profile:SearchProfileVO):void {
			ArthropodDebug.log("addSearchProfile");
			if(addSearchProfileStatement) {
				addSearchProfileStatement.parameters["@title"] = profile.title;
				addSearchProfileStatement.parameters["@url"] = profile.url;
				addSearchProfileStatement.execute( -1, new Responder(onAddSearchProfileResult, onError));
			}
		}
		
		public function getSearchProfile(profileID:int):void{
			ArthropodDebug.log("getAllSearchProfiles");
			if (getSearchProfileStatement) {
				getSearchProfileStatement.parameters["@profileID"] = profileID;
				getSearchProfileStatement.execute(-1,new Responder(onGetSearchProfileResult, onError));
			}
		}
		
		
	//	WORDS
		public function getAllWordsInSession(sessionID:int):void {
			ArthropodDebug.log("getAllWordsInSession");
			if(getAllSessionsStatement) {
				getAllWordsInSesionStatement.parameters["@sessionID"] = sessionID;
				getAllWordsInSesionStatement.execute( -1, new Responder(onAllWordsInSessionResult, onError));
			}
		}
		
		public function addWord(word:WordVO):void {
			ArthropodDebug.log("addWord " + word);
			addWordStatement.parameters["@word"] = word.word;
			addWordStatement.parameters["@translation"] = word.translation;
			addWordStatement.execute(-1,new Responder(onWordAdded, onError));
		}
		
		public function addWordToSession(wordID:int, sessionID:int):void {
			ArthropodDebug.log("addWordToSession " + wordID);
			addWordToSesionStatement.parameters["@wordID"] = wordID;
			addWordToSesionStatement.parameters["@sessionID"] = sessionID;
			addWordToSesionStatement.execute(-1,new Responder(onWordAddedToSession, onError));
		}
		
		public function getSingleWord(wordID:int):void {
			ArthropodDebug.log("getAllWordsInSession");
			if(getSingleWordStatement) {
				getSingleWordStatement.parameters["@wordID"] = wordID;
				getSingleWordStatement.execute( -1, new Responder(onSingleWordResult, onError));
			}
		}
		
		public function addTranslation(wordID:int, translation:String):void {
			ArthropodDebug.log("addTranslation " + wordID);
			addTranslationStatement.parameters["@wordID"] = wordID;
			addTranslationStatement.parameters["@translation"] = translation;
			addTranslationStatement.execute( -1, new Responder(onTranslationAdded, onError));
			
			_resultUsage = USAGE_UPDATE;
			getSingleWord(wordID);
		}
		
		
	/************************************
	/*	PRIVATE METHODS
	/***********************************/
		private function onDbOpened(e:SQLEvent):void {
			ArthropodDebug.log("onDbOpened " + e.toString());
			ArthropodDebug.log("_dbExists " + _dbExists);
			
			if (!_dbExists ) {
				ArthropodDebug.log("creating tables");
				var populateDB:SQLStatement = new SQLStatement();
				populateDB.sqlConnection = _dbConn;
				_dbConn.begin();
				
				populateDB.text = "CREATE TABLE \"search_profile\" (\"id\" INTEGER PRIMARY KEY NOT NULL  DEFAULT '1' , \"title\" TEXT NOT NULL  DEFAULT '', \"url\" TEXT NOT NULL  DEFAULT '');";
				populateDB.execute(-1, new Responder(onPopulateResult, onError));
				populateDB.text = "CREATE TABLE \"word_session\" (\"session_id\" INTEGER NOT NULL , \"word_id\" INTEGER NOT NULL , PRIMARY KEY (\"session_id\", \"word_id\") );";
				populateDB.execute(-1, new Responder(onPopulateResult, onError));
				populateDB.text = "CREATE TABLE \"word\" (\"id\" INTEGER PRIMARY KEY NOT NULL  DEFAULT '1' , \"word\" TEXT NOT NULL  DEFAULT '', \"translation\" TEXT NOT NULL  DEFAULT '', \"created\" DATETIME NOT NULL  DEFAULT '', \"updated\" DATETIME NOT NULL  DEFAULT '');";
				populateDB.execute(-1, new Responder(onPopulateResult, onError));
				populateDB.text = "CREATE TABLE \"session\" (\"id\" INTEGER PRIMARY KEY NOT NULL  DEFAULT '1' , \"title\" TEXT NOT NULL  DEFAULT '', \"created\" DATETIME NOT NULL  DEFAULT '', \"updated\" DATETIME NOT NULL  DEFAULT '');";
				populateDB.execute(-1, new Responder(onPopulateResult, onError));
				
				populateDB.text = "INSERT INTO \"search_profile\" VALUES(1,'None','none');";
				populateDB.execute(-1, new Responder(onPopulateResult, onError));
				populateDB.text = "INSERT INTO \"search_profile\" VALUES(2,'FR Wikipedia','http://fr.wikipedia.org/wiki/Special:Recherche?search=#PHRASE#');";
				populateDB.execute(-1, new Responder(onPopulateResult, onError));
				populateDB.text = "INSERT INTO \"search_profile\" VALUES(3,'FR Wiktionary','http://fr.wiktionary.org/wiki/Special:Search?search=#PHRASE#');";
				populateDB.execute(-1, new Responder(onPopulateResult, onError));
				populateDB.text = "INSERT INTO \"search_profile\" VALUES(4,'FR Google Define','http://www.google.com/search?hl=fr&q=define:#PHRASE#');";
				populateDB.execute(-1, new Responder(onPopulateResult, onError));
				populateDB.text = "INSERT INTO \"search_profile\" VALUES(5,'Google','http://www.google.com/search?q=#PHRASE#');";
				populateDB.execute(-1, new Responder(onPopulateResult, onError));
				populateDB.text = "INSERT INTO \"search_profile\" VALUES(6,'FR Wordreference','http://www.wordreference.com/fren/#PHRASE#');";
				populateDB.execute( -1, new Responder(onPopulateResult, onError));
				
				_dbConn.commit();
				ArthropodDebug.log("_dbConn commited");
			}
			
			
			var querry:String;
			
			getAllSessionsStatement = new SQLStatement();
			getAllSessionsStatement.itemClass = SessionVO;
			getAllSessionsStatement.sqlConnection = _dbConn;
			querry = "SELECT * FROM session";
			getAllSessionsStatement.text = querry;
			
			createSessionStatement = new SQLStatement();
			createSessionStatement.sqlConnection = _dbConn;
			querry = "INSERT INTO session (title, created, updated) VALUES(@title, current_timestamp, current_timestamp)";
			createSessionStatement.text = querry;
			
			
			getAllSearchProfilesStatement = new SQLStatement();
			getAllSearchProfilesStatement.itemClass = SearchProfileVO;
			getAllSearchProfilesStatement.sqlConnection = _dbConn;
			querry = "SELECT * FROM search_profile";
			getAllSearchProfilesStatement.text = querry;
			
			addSearchProfileStatement = new SQLStatement();
			addSearchProfileStatement.itemClass = SearchProfileVO;
			addSearchProfileStatement.sqlConnection = _dbConn;
			querry = "INSERT INTO search_profile (title, url) VALUES(@title, @url)";
			addSearchProfileStatement.text = querry;
			
			getSearchProfileStatement = new SQLStatement();
			getSearchProfileStatement.itemClass = SearchProfileVO;
			getSearchProfileStatement.sqlConnection = _dbConn;
			querry = "SELECT * FROM search_profile WHERE id=@profileID LIMIT 1";
			getSearchProfileStatement.text = querry;
			
			
			getAllWordsInSesionStatement = new SQLStatement();
			getAllWordsInSesionStatement.itemClass = WordVO;
			getAllWordsInSesionStatement.sqlConnection = _dbConn;
			querry = "SELECT word.id, word.word, word.translation, word.created, word.updated FROM word, session, word_session WHERE word.id=word_session.word_id AND session.id=word_session.session_id AND session.id=@sessionID";
			getAllWordsInSesionStatement.text = querry;
			
			getSingleWordStatement = new SQLStatement();
			getSingleWordStatement.itemClass = WordVO;
			getSingleWordStatement.sqlConnection = _dbConn;
			querry = "SELECT * FROM word WHERE id=@wordID LIMIT 1";
			getSingleWordStatement.text = querry;
			
			addWordStatement = new SQLStatement();
			addWordStatement.sqlConnection = _dbConn;
			querry = "INSERT INTO word (word, translation, created, updated) VALUES(@word, @translation, current_timestamp, current_timestamp)";
			addWordStatement.text = querry;
			
			addWordToSesionStatement = new SQLStatement();
			addWordToSesionStatement.sqlConnection = _dbConn;
			querry = "INSERT INTO word_session (session_id, word_id) VALUES(@sessionID, @wordID)";
			addWordToSesionStatement.text = querry;
			
			addTranslationStatement = new SQLStatement();
			addTranslationStatement.sqlConnection = _dbConn;
			querry = "UPDATE word SET translation=@translation, updated=current_timestamp WHERE id=@wordID";
			addTranslationStatement.text = querry;
			
			sendNotification(READY, this);
		}
		
		private function onPopulateResult(result:SQLResult):void{
			ArthropodDebug.log("onPopulateResult " + result);
		}
		
		private function onDbOpenError(e:SQLError):void {
			ArthropodDebug.error("onDbOpenError " + e.toString());
		}
		
		private function onError(error:SQLError):void {
			ArthropodDebug.error("onError " + error);
		}
		
		
		
	/************************************
	/*	RESULT HANDLERS
	/***********************************/
		private function onAllSessionsResult(result:SQLResult):void {
			ArthropodDebug.log("onAllSessionsResult " + result);
			ArthropodDebug.array(result.data);
			var data:Array = result.data;
			allSessions.removeAll();
			if(data) {
				for (var i:int = 0; i < data.length; i++) {
					ArthropodDebug.log("adding " +data[i]);
					allSessions.addItem(data[i]);
				}
				allSessions = allSessions;
			}
		}
		private function onAllSearchProfilesResult(result:SQLResult):void {
			ArthropodDebug.log("onAllSearchProfilesResult " + result);
			ArthropodDebug.array(result.data);
			var data:Array = result.data;
			allSearchProfiles.removeAll();
			if(data) {
				for (var i:int = 0; i < data.length; i++) {
					ArthropodDebug.log("addind " +data[i]);
					allSearchProfiles.addItem(data[i]);
				}
				allSearchProfiles = allSearchProfiles;
			}
		}
		
		private function onAddSearchProfileResult(result:SQLResult):void {
			ArthropodDebug.log("onAddSearchProfileResult " + result);
			_resultUsage = USAGE_ADD;
			getSearchProfile(result.lastInsertRowID);
		}
		
		private function onGetSearchProfileResult(result:SQLResult):void {
			ArthropodDebug.log("onGetSearchProfileResult " + result);
			var data:Array = result.data;
			if(data) {
				for (var i:int = 0; i < data.length; i++) {
					ArthropodDebug.log("_resultUsage " + _resultUsage + " " + data[i]);
					if (_resultUsage == USAGE_ADD) {
						allSearchProfiles.addItem(data[i]);
					} else if(_resultUsage == USAGE_UPDATE) {
						//var oldIndex:int = allSearchProfiles.getIndexByID((data[i] as WordVO).id);
						//allSearchProfiles.replaceItemAt(data[i], oldIndex);
					}
				}
				_resultUsage = null;
				allSearchProfiles = allSearchProfiles;
			}
		}
		
		private function onSessionCreated(result:SQLResult):void {
			ArthropodDebug.error("onSessionCreated " + result);
			getAllSessions();
			getAllWordsInSession(_selectedSession);
		}
		
		private function onAllWordsInSessionResult(result:SQLResult):void {
			ArthropodDebug.log("onAllSessionsResult " + result);
			var data:Array = result.data;
			wordsSession.removeAll();
			if(data) {
				for (var i:int = 0; i < data.length; i++) {
					ArthropodDebug.log("addind " +data[i]);
					trace("addind " +data[i]);
					wordsSession.addItem(data[i]);
				}
				wordsSession = wordsSession;
			}
		}
		
		private function onSingleWordResult(result:SQLResult):void {
			ArthropodDebug.log("onAllSessionsResult " + result);
			var data:Array = result.data;
			if(data) {
				for (var i:int = 0; i < data.length; i++) {
					ArthropodDebug.log("_resultUsage " + _resultUsage + " " + data[i]);
					if (_resultUsage == USAGE_ADD) {
						wordsSession.addItem(data[i]);
					} else if(_resultUsage == USAGE_UPDATE) {
						var oldIndex:int = wordsSession.getIndexByID((data[i] as WordVO).id);
						wordsSession.replaceItemAt(data[i], oldIndex);
					}
				}
				_resultUsage = null;
				wordsSession = wordsSession;
			}
		}
		
		private function onWordAdded(result:SQLResult):void {
			ArthropodDebug.error("onWordAdded " + result);
			addWordToSession(result.lastInsertRowID, _selectedSession);
			_resultUsage = USAGE_ADD;
			getSingleWord(result.lastInsertRowID);
		}
		
		private function onWordAddedToSession(result:SQLResult):void {
			ArthropodDebug.error("onWordAddedToSession " + result);
		}
		
		private function onTranslationAdded(result:SQLResult):void {
			ArthropodDebug.error("onTranslationAdded " + result);
		}
		
		
	/************************************
	/*	GET & SET METHODS
	/***********************************/
		public function get selectedSession():int { return _selectedSession; }
		
		public function set selectedSession(value:int):void {
			_selectedSession = value;
			getAllWordsInSession(_selectedSession);
			appProxy.appState = AppProxy.WATCH_WORDS;
		}

	
		
	}
}